﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace RefreshDataClient
{
    /// <summary>/// 数据库帮助类
    /// </summary>
    public class DBHelper
    {
        protected static string ConnStr = "Data Source=.;Initial Catalog =AIS20240924185802;User ID=sa;Pwd=WSDZ@2024;";
        /// <summary>
        /// 执行添加、删除、修改的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                //打开数据库连接
                conn.Open();
                //创建执行脚本的对象SQL
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    command.Parameters.AddRange(paras);
                    command.CommandTimeout = 300;
                    int result = command.ExecuteNonQuery();//执行
                    return result;
                }
            }
        }
        /// <summary>
        /// 执行sql集合，不开启事务
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryAllNoTran(List<string> sqlList, params SqlParameter[] paras)
        {
            int res = 0;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                //打开数据库连接
                conn.Open();
                try
                {
                    foreach (var sql in sqlList)
                    {
                        //创建执行脚本的对象SQL
                        using (SqlCommand command = new SqlCommand(sql, conn))
                        {
                            command.Parameters.AddRange(paras);
                            command.CommandTimeout = 1800;
                            int result = command.ExecuteNonQuery();//执行
                            res += result;
                        }
                    }
                }
                catch (Exception ex)
                {
                    conn.Close();
                }
            }
            return res;
        }

        /// <summary>
        /// 执行sql集合，开启事务
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryByTransaction(List<string> sqlList,params SqlParameter[] paras)
        {
            int res = 0;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                //打开数据库连接
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in sqlList)
                        {
                            //创建执行脚本的对象SQL
                            using (SqlCommand command = new SqlCommand(sql, conn))
                            {
                                command.Parameters.AddRange(paras);
                                command.Transaction = tran;
                                command.CommandTimeout = 1800;
                                int result = command.ExecuteNonQuery();//执行
                                res += result;
                            }  
                        }
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        conn.Close();
                    }
                }
            }
            return res;
        }

        public static bool SqlBulkCopyInsert(List<DataTable> dts) 
        {
            using (SqlConnection conn = new SqlConnection(ConnStr)) 
            {
                conn.Open();
                using (SqlTransaction tran= conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default,tran))
                        {
                            foreach (var dt in dts)
                            {
                                if (dt!=null&&dt.Rows.Count>0)
                                {
                                    foreach (DataColumn dc in dt.Columns)
                                        sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dc.ColumnName, dc.ColumnName));
                                    sqlBulkCopy.DestinationTableName = dt.TableName;
                                    sqlBulkCopy.BatchSize = dt.Rows.Count;
                                    sqlBulkCopy.BulkCopyTimeout = 600;
                                    sqlBulkCopy.WriteToServer(dt);
                                    sqlBulkCopy.ColumnMappings.Clear();
                                }
                            }
                            tran.Commit();
                            return true;
                        }
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        tran.Dispose();
                        conn.Close();
                        //父方法名
                        System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace(true);
                        System.Reflection.MethodBase mb = st.GetFrame(1).GetMethod();
                        string methodPath=$@"{mb.DeclaringType.FullName};{mb.Name}";
                        return false;
                    }
                   
                }
            }
           
        }

        /// <summary>
        /// 执行SQL并返回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] paras)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sql, conn))
                    {
                        command.Parameters.AddRange(paras);
                        command.CommandTimeout = 300;
                        object obj = command.ExecuteScalar();
                        return obj;
                    }
                  
                }
            }
            catch (Exception ex)
            {
                return null;
            }
           
        }
        public static object ExecuteScalarV1(string sql, object defaultValue, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    command.Parameters.AddRange(paras);
                    command.CommandTimeout = 300;
                    object obj = command.ExecuteScalar();
                    return obj ?? defaultValue;
                }
            }
        }
        public static T ExecuteScalarV2<T>(string sql, T t,params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    command.Parameters.AddRange(paras);
                    command.CommandTimeout = 300;
                    object obj = command.ExecuteScalar();
                    return obj == null ? t : (T)obj;
                }
            }
        }
        /// <summary>
        /// 执行SQL返回SqlDataReader对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            command.Parameters.AddRange(paras);
            command.CommandTimeout = 300;
            return command.ExecuteReader(CommandBehavior.CloseConnection);//命令行为

        }
        public static DataRow GetFirstDataRow(string sql, params SqlParameter[] paras)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                command.CommandTimeout = 300;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dt = new DataTable();
                adapter.Fill(dt);
            }
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }
        public static DataTable GetDataTable(string sql, params SqlParameter[] paras)
        {
            try
            {
                DataTable dt = null;
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    SqlCommand command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(paras);
                    command.CommandTimeout = 300;
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    dt = new DataTable();
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw;
            }
          
        }
        public static DataSet GetDataSet(string sql, params SqlParameter[] paras)
        {
            DataSet ds = null;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                command.CommandTimeout = 300;
                //关联SqlServer 和 DataSet SqlCommandBuilder
                SqlDataAdapter adapter = new SqlDataAdapter(command);//适配器
                ds = new DataSet();
                adapter.Fill(ds);
            }
            return ds;
        }
        /// <summary>
        /// 获取适配器
        /// SqlCommandBuilder，SqlDataAdapter 组合使用
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataAdapter GetDataAdapter(string sql, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand command = new SqlCommand(sql, conn);
            command.Parameters.AddRange(paras);
            command.CommandTimeout = 300;
            SqlDataAdapter adapter = new SqlDataAdapter(command);

            return adapter;
        }


    }
}
